package com.pe.pgn.clubpgn.dao.impl;

import java.util.Calendar;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.apache.commons.lang.StringUtils;
import org.hibernate.Criteria;
import org.hibernate.criterion.DetachedCriteria;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Restrictions;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import com.pe.pgn.clubpgn.common.CLPConstantes;
import com.pe.pgn.clubpgn.dao.AlmacenDao;
import com.pe.pgn.clubpgn.dao.hibernate.GenericDaoHibernate;
import com.pe.pgn.clubpgn.domain.ClpbAlmacen;

@Repository("almacenDao")
public class AlmacenDaoImpl extends GenericDaoHibernate<ClpbAlmacen, Long> implements
		AlmacenDao {

	private JdbcTemplate jdbcTemplate;
	
	@Autowired
	public void setDataSource(DataSource dataSource) {
		this.jdbcTemplate = new JdbcTemplate(dataSource);
	}

	public AlmacenDaoImpl(){
		super(ClpbAlmacen.class);
	}

	
	@SuppressWarnings("unchecked")
	public List<Map<String, Object>> buscarAlmacenes(String deAlmacen,
			String coCodigoAlmacen, Long coEstacion, String stAlmacen,
			String stTipoAlmacen) {
		
		StringBuffer query = new StringBuffer();
		query.append("select a.id as id , ");
		query.append("a.de_almacen as deAlmacen, ");		
		query.append("		a.co_codigo_almacen as coCodigoAlmacen, ");
		query.append("		e.de_estacion as deEstacion, ");
		query.append("		a.st_es_almacen_principal as stEsAlmacenPrincipal,"); 
		query.append("		a.st_es_almacen_virtual as stEsAlmacenVirtual, ");
		query.append("		a.st_es_punto_canje as stEsPuntoCanje, ");
		query.append("		a.st_almacen as stAlmacen ");
		query.append("	from clpb_almacen a join clpb_estacion e on a.co_estacion = e.id "); 
		query.append("	where 1 = 1 ");
		
		if(StringUtils.isNotBlank(deAlmacen)){
			query.append("		and  upper(trim(a.de_almacen)) like '%").append(deAlmacen.toUpperCase()).append("%'");
		}
		
		if(StringUtils.isNotBlank(coCodigoAlmacen)){
			query.append("		and upper(trim(a.co_codigo_almacen)) like '%").append(coCodigoAlmacen.toUpperCase()).append("%'");
		}
		
		if(coEstacion > 0){
			query.append("		and a.co_estacion = ").append(coEstacion);
		}
		
		if(StringUtils.isNotBlank(stAlmacen) && !stAlmacen.equalsIgnoreCase(CLPConstantes.CARACTER_GUION_NO_ESPACIOS)){
			query.append("		and a.st_almacen = '").append(stAlmacen).append("' ");
		}
		
		if(StringUtils.isNotBlank(stTipoAlmacen) && !stTipoAlmacen.equalsIgnoreCase(CLPConstantes.CARACTER_GUION_NO_ESPACIOS)){
		
			if(stTipoAlmacen.equalsIgnoreCase(CLPConstantes.ST_MANEJA_STOCK_ALMACEN_PRINCIPAL)){
				query.append("	and a.st_es_almacen_principal = '").append(CLPConstantes.ST_VERDADERO).append("' ");
			}
			else if(stTipoAlmacen.equalsIgnoreCase(CLPConstantes.ST_NO_MANEJA_STOCK)){
				query.append("	and a.st_es_almacen_virtual = '").append(CLPConstantes.ST_VERDADERO).append("' ");
			}
			else if(stTipoAlmacen.equalsIgnoreCase(CLPConstantes.ST_MANEJA_STOCK_PUNTO_CANJE)){
				query.append("	and a.st_es_punto_canje  = '").append(CLPConstantes.ST_VERDADERO).append("' ");
			}
		}
		log.info(query.toString());
		List lista = findListOfMaps(query.toString(), null);
		return lista;
	}

	
	public void eliminarAlmacen(Long id) {
		
		remove(id);
		getHibernateTemplate().flush();		
	}

	
	public boolean esAlmacenConDependencias(Long id) {
		
		boolean esAlmacenConStock = esAlmacenConStock(id);
		boolean esAlmacenConTransacciones = esAlmacenConTransacciones(id);
		boolean esAlmacenConDocumentos = esAlmacenConDocumentos(id);
		
		if(esAlmacenConStock || esAlmacenConTransacciones || esAlmacenConDocumentos){
			return true;
		}		
		return false;
	}
	
	public boolean esAlmacenConStock(Long id){
		
		StringBuffer query = new StringBuffer();		
		query.append("select count(*) ");
		query.append("	from clpm_stock_almacen_catalogo ");
		query.append("	where co_almacen = ").append(id);
		query.append("		and st_stock_almacen_catalogo = '").append(CLPConstantes.ST_VERDADERO).append("' ");
		
		int count = jdbcTemplate.queryForInt(query.toString());
		if(count > 0){
			return true;
		}		
		return false;
	}
	
	public boolean esAlmacenConTransacciones(Long id){
		
		StringBuffer query = new StringBuffer();	
		query.append("select count(*) ");
		query.append("	from clpm_transaccion_articulo ");
		query.append("	where co_almacen = ").append(id);
		
		int count = jdbcTemplate.queryForInt(query.toString());
		if(count > 0){
			return true;
		}		
		return false;
	}
	
	public boolean esAlmacenConDocumentos(Long id){
		
		StringBuffer query = new StringBuffer();		
		query.append("select count(*) ");
		query.append("	from clpb_documento_mov_articulo ");
		query.append("	where co_almacen = ").append(id);
		query.append("		or co_almacen_destino = ").append(id);
		query.append("		or co_almacen_origen = ").append(id);
		
		int count = jdbcTemplate.queryForInt(query.toString());
		if(count > 0){
			return true;
		}		
		return false;
	}

	
	public void guardarAlmacen(ClpbAlmacen almacen) {
		
		if(almacen.getId() == null){
			
			almacen.setCoUsuarioCreador(getUsuarioLogueado().getUsername());
			almacen.setDaFechaCreacion(Calendar.getInstance());
			almacen.setStAlmacen(Boolean.TRUE);			
		}
		else{
			almacen.setCoUsuarioModificador(getUsuarioLogueado().getUsername());
			almacen.setDaFechaCreacion(Calendar.getInstance());
		}
		
		getHibernateTemplate().saveOrUpdate(almacen);
		getHibernateTemplate().flush();
	}

	
	public ClpbAlmacen obtenerAlmacen(Long id) {
		
		ClpbAlmacen almacen = get(id);
		return almacen;
	}

	
	@SuppressWarnings("unchecked")
	public List<ClpbAlmacen> obtenerAlmacenes(boolean esInicial) {
		
		Criteria criteria = DetachedCriteria.forClass(ClpbAlmacen.class)
					.getExecutableCriteria(getSessionFactory().getCurrentSession());

		if(esInicial){
			criteria.setMaxResults(CLPConstantes.MAXIMO_RESULTADO_INICIAL);
		}
		
		criteria.add(Restrictions.eq("stAlmacen", Boolean.TRUE));
		criteria.addOrder(Order.asc("deAlmacen"));
		
		List<ClpbAlmacen> lista = criteria.list(); 
		return lista;
	}
	
	public boolean existeAlmacenPrincipal(Long id){
		
		StringBuffer query = new StringBuffer();
		query.append("	select count(*) ");
		query.append("	from clpb_almacen ");
		query.append("	where st_es_almacen_principal = '").append(CLPConstantes.ST_VERDADERO).append("' ");
		
		if(id != null){
			query.append(" and id != ").append(id);
		}
		
		query.append("		and st_es_almacen_virtual = '").append(CLPConstantes.ST_FALSO).append("' ");
		query.append("		and st_es_punto_canje = '").append(CLPConstantes.ST_FALSO).append("' ");
		query.append("		and st_almacen = '").append(CLPConstantes.ST_VERDADERO).append("' ");
		
		int count = jdbcTemplate.queryForInt(query.toString());
		if(count > 0){
			return true;
		}
		return false;
	}
	
	public boolean existeAlmacenVirtual(Long id){
		
		StringBuffer query = new StringBuffer();
		query.append("	select count(*) ");
		query.append("	from clpb_almacen ");
		query.append("	where st_es_almacen_principal = '").append(CLPConstantes.ST_FALSO).append("' ");
		
		if(id != null){
			query.append(" and id != ").append(id);
		}
		query.append("		and st_es_almacen_virtual = '").append(CLPConstantes.ST_VERDADERO).append("' ");
		query.append("		and st_es_punto_canje = '").append(CLPConstantes.ST_FALSO).append("' ");
		query.append("		and st_almacen = '").append(CLPConstantes.ST_VERDADERO).append("' ");
		
		int count = jdbcTemplate.queryForInt(query.toString());
		if(count > 0){
			return true;
		}
		return false;
	}	
	
	@SuppressWarnings("unchecked")
	public ClpbAlmacen obtenerAlmacenPrincipal(){
		
		Criteria criteria = DetachedCriteria.forClass(ClpbAlmacen.class)
			.getExecutableCriteria(getSessionFactory().getCurrentSession());

		criteria.add(Restrictions.eq("stEsAlmacenVirtual", Boolean.FALSE));
		criteria.add(Restrictions.eq("stEsAlmacenPrincipal", Boolean.TRUE));
		criteria.add(Restrictions.eq("stEsPuntoCanje", Boolean.FALSE));
		criteria.add(Restrictions.eq("stAlmacen", Boolean.TRUE));
				
		List<ClpbAlmacen> lista = criteria.list();
		if(!lista.isEmpty()){
			return lista.get(0);
		}
		
		return null;				
	}
	
	@SuppressWarnings("unchecked")
	public ClpbAlmacen obtenerAlmacenVirtual(){
		
		Criteria criteria = DetachedCriteria.forClass(ClpbAlmacen.class)
			.getExecutableCriteria(getSessionFactory().getCurrentSession());

		criteria.add(Restrictions.eq("stEsAlmacenVirtual", Boolean.TRUE));
		criteria.add(Restrictions.eq("stEsAlmacenPrincipal", Boolean.FALSE));
		criteria.add(Restrictions.eq("stEsPuntoCanje", Boolean.FALSE));
		criteria.add(Restrictions.eq("stAlmacen", Boolean.TRUE));
				
		List<ClpbAlmacen> lista = criteria.list();
		if(!lista.isEmpty()){
			return lista.get(0);
		}
		
		return null;				
	}	
	
	public boolean esEstacionUsada(Long coEstacion, Long id){
		
		StringBuffer query = new StringBuffer();		
		query.append("select count(*) ");
		query.append("	from clpb_almacen ");
		query.append("	where co_estacion = ").append(coEstacion);
		
		if(id != null){
			query.append("		and id != ").append(id);
		}
		
		query.append("		and st_almacen = '").append(CLPConstantes.ST_VERDADERO).append("' ");
		
		int count = jdbcTemplate.queryForInt(query.toString());
		if(count > 0){
			return true;
		}
		
		return false;
	}	

}

